﻿using System;
using System.Collections.Generic;
using System.Text;
using Net4.Common.Services;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using System.Data;
using Net4.Common.Entities;
using Net4.Common.Data;

namespace Net4.Services.Access
{
    public class AccessArticleService
    {
        private static AccessArticleService _Instance = new AccessArticleService();

        public static AccessArticleService Instance
        {
            get { return AccessArticleService._Instance; }
        }

        #region AccessArticleService Members

        private static readonly string INSERT_ARTICLE =
@"INSERT INTO [Articles]([Author], [Content], [CreateDate], [Description], [Hits], [Photos], [Tags], [Title], [Category]) 
    VALUES(@Author, @Content, @CreateDate, @Description, @Hits, @Photos, @Tags, @Title, @Category)";
        /// <summary>
        /// Inserts the specified article.
        /// </summary>
        /// <param name="article">The article.</param>
        public void Insert(Article article)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(INSERT_ARTICLE);
            db.AddInParameter(cmd, "@Author", DbType.String, article.Author);
            db.AddInParameter(cmd, "@Content", DbType.String, article.Content);
            db.AddInParameter(cmd, "@CreateDate", DbType.DateTime, article.CreateDate.ToString());
            db.AddInParameter(cmd, "@Description", DbType.String, article.Description);
            db.AddInParameter(cmd, "@Hits", DbType.Int32, article.Hits);
            db.AddInParameter(cmd, "@Photos", DbType.String, article.Photos);
            db.AddInParameter(cmd, "@Tags", DbType.String, article.Tags);
            db.AddInParameter(cmd, "@Title", DbType.String, article.Title);
            db.AddInParameter(cmd, "@Category", DbType.Int32, article.Category);

            //要使Select @@IDENTITY返回正确的值，必须保证在同一事务内完成
            using (DbConnection connection = db.CreateConnection())
            {
                connection.Open();
                DbTransaction transaction = connection.BeginTransaction();

                try
                {
                    // insert the first message.
                    db.ExecuteNonQuery(cmd, transaction);

                    cmd.Parameters.Clear();
                    cmd.CommandText = Utilis.GET_LAST_INSERT_IDENTITY;
                    article.Id = (int)db.ExecuteScalar(cmd, transaction);

                    // Commit the transaction.
                    transaction.Commit();
                }
                catch(Exception e)
                {
                    // Roll back the transaction. 
                    transaction.Rollback();
                    throw e;
                }
                finally
                {
                    connection.Close();
                }
            }
            
        }

        private static readonly string UPDATE_ARTICLE =
@"UPDATE [Articles] SET [Author]=@Author, [Content]=@Content, [Description]=@Description, 
    [Hits]=@Hits, [LastModifiedDate]=@LastModifiedDate, [Photos]=@Photos, 
    [Tags]=@Tags, [Title]=@Title, [Category]=@Category WHERE [Id]=@Id";
        /// <summary>
        /// Updates the specified article.
        /// </summary>
        /// <param name="article">The article.</param>
        public void Update(Article article)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(UPDATE_ARTICLE);
            db.AddInParameter(cmd, "@Author", DbType.String, article.Author);
            db.AddInParameter(cmd, "@Content", DbType.String, article.Content);
            db.AddInParameter(cmd, "@Description", DbType.String, article.Description);
            db.AddInParameter(cmd, "@Hits", DbType.Int32, article.Hits);
            db.AddInParameter(cmd, "@LastModifiedDate", DbType.DateTime, article.LastModifiedDate.ToString());
            db.AddInParameter(cmd, "@Photos", DbType.String, article.Photos);
            db.AddInParameter(cmd, "@Tags", DbType.String, article.Tags);
            db.AddInParameter(cmd, "@Title", DbType.String, article.Title);
            db.AddInParameter(cmd, "@Category", DbType.Int32, article.Category);

            db.AddInParameter(cmd, "@Id", DbType.Int32, article.Id);

            db.ExecuteNonQuery(cmd);
        }

        private static readonly string DELETE_ARTICLE =
@"DELETE FROM [Articles] WHERE [Id]=@Id";
        private static readonly string DELETE_ARTICLE_ATTACHMENTS =
@"DELETE FROM [Attachments] WHERE [ParentId]=@Id";
        /// <summary>
        /// Deletes the specified article id.
        /// </summary>
        /// <param name="articleId">The article id.</param>
        public void Delete(int articleId)
        {
            List<Attachment> attachments = AccessFileService.Instance.SelectAttachments(articleId);
            foreach (Attachment item in attachments)
            {
                AttachmentManager.Instance.DeleteAttachment(item.FileName);
            }
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(DELETE_ARTICLE);
            db.AddInParameter(cmd, "@Id", DbType.Int32, articleId);

            using (DbConnection connection = db.CreateConnection())
            {
                connection.Open();
                DbTransaction transaction = connection.BeginTransaction();

                try
                {
                    // delete article
                    db.ExecuteNonQuery(cmd, transaction);

                    cmd.CommandText = DELETE_ARTICLE_ATTACHMENTS;
                    //delete attachments
                    db.ExecuteNonQuery(cmd, transaction);

                    // Commit the transaction.
                    transaction.Commit();
                }
                catch (Exception e)
                {
                    // Roll back the transaction. 
                    transaction.Rollback();
                    throw e;
                }
                finally
                {
                    connection.Close();
                }
            }
        }

        private static readonly string SELECT_ARTICLE =
@"SELECT * FROM [Articles] WHERE [Id]=@Id";
        /// <summary>
        /// Selects the specified article id.
        /// </summary>
        /// <param name="articleId">The article id.</param>
        /// <returns></returns>
        public Article Select(int articleId)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(SELECT_ARTICLE);
            db.AddInParameter(cmd, "@Id", DbType.Int32, articleId);

            Article art = null;
            using (SafeDataReader reader = new SafeDataReader(db.ExecuteReader(cmd)))
            {
                if (reader.Read())
                {
                    art = new Article(reader.GetInt32("Id"),
                        reader.GetString("Title"),
                        reader.GetString("Author"),
                        reader.GetDateTime("CreateDate"),
                        reader.GetDateTime("LastModifiedDate"),
                        reader.GetInt32("Hits"),
                        reader.GetString("Tags"),
                        reader.GetString("Content"),
                        reader.GetString("Description"),
                        reader.GetString("Photos"),
                        (Category)reader.GetInt32("Category"));
                }
            }

            if (art != null)
            {
                art.Attachments = AccessFileService.Instance.SelectAttachments(art.Id);
            }

            return art;
        }

        private static readonly string SELECT_ARTICLELIST_PAGE =
@"SELECT TOP {0} * FROM [Articles] 
WHERE [Category]=@category AND (
    [Id] < (SELECT MIN([Id]) FROM (
        SELECT TOP {1} [Id] FROM [Articles] [Category]=@category ORDER BY [Id] DESC) AS T)
)
ORDER BY [Id] DESC
";
        private static readonly string SELECT_ARTICLELIST =
@"SELECT TOP {0} * FROM [Articles] WHERE [Category]=@category ORDER BY [Id] DESC";

        /// <summary>
        /// Selects the articles.
        /// </summary>
        /// <param name="category">The category.</param>
        /// <param name="pageIndex">Index of the page, base 1.</param>
        /// <param name="pageMaxCount">The page max count.</param>
        /// <returns></returns>
        public List<Article> SelectArticles(Category category, int pageIndex, int pageMaxCount)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(string.Format(SELECT_ARTICLELIST, pageMaxCount));
            if (pageIndex > 1)
            {
                cmd.CommandText = string.Format(SELECT_ARTICLELIST_PAGE, pageMaxCount, (pageIndex - 1) * pageMaxCount);
            }
            db.AddInParameter(cmd, "@category", DbType.Int32, category);
            using (SafeDataReader reader = new SafeDataReader(db.ExecuteReader(cmd)))
            {
                List<Article> list = new List<Article>();
                while (reader.Read())
                {
                    Article art = new Article(reader.GetInt32("Id"),
                        reader.GetString("Title"),
                        reader.GetString("Author"),
                        reader.GetDateTime("CreateDate"),
                        reader.GetDateTime("LastModifiedDate"),
                        reader.GetInt32("Hits"),
                        reader.GetString("Tags"),
                        reader.GetString("Content"),
                        reader.GetString("Description"),
                        reader.GetString("Photos"),
                        (Category)reader.GetInt32("Category"));
                    list.Add(art);
                }
                return list;
            }
        }

        private static readonly string GET_ARTICLE_TOTALCOUNT = @"SELECT COUNT([Id]) FROM [Articles] WHERE [Category]=@category";
        /// <summary>
        /// Gets the articles total count.
        /// </summary>
        /// <param name="category">The category.</param>
        /// <returns></returns>
        public int GetArticlesTotalCount(Category category)
        { 
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(GET_ARTICLE_TOTALCOUNT);
            db.AddInParameter(cmd, "@category", DbType.Int32, category);
            return (int)db.ExecuteScalar(cmd);
        }

        #endregion
    }
}
